<?php
require_once 'application/library/db/gcBasicDao.php';
require_once 'application/constant/dbConstant.php';

class dao_T_EC_M_GOODSDao extends gcBasicDao {
    protected $_table = "T_EC_M_GOODS";

    public function pbGetCount() {
        $sql = "select count(*) from T_EC_M_GOODS as A left join (SELECT "
                           . "COUNT(E.SUPPLIER_NO) AS SUPPLIERNUM, "
                           . "GROUP_CONCAT(F.SUPPLIER_NAME SEPARATOR '、') AS NAME_GROUP, "
                           . "E.GOODS_NO "
                           . "FROM T_EC_M_GOODS_REALATED_SUPPLIER AS E "
                           . "LEFT OUTER JOIN T_AD_M_SUPPLIER AS F ON E.SUPPLIER_NO = F.SUPPLIER_NO "
                           . "GROUP BY E.GOODS_NO "
                           . "ORDER BY E.GOODS_NO "
                           . ") "
                           . " AS B ON A.GOODS_NO = B.GOODS_NO 
                           where GOODS_TP = '1' ";
        $count = $this->select($sql, null, dbReturnType::ONE);
        return $count;
    }

    public function pbGetGoods() {
        $sql = "select A.GOODS_CODE, A.GOODS_NO, A.GOODS_NAME,A.VARIATION, A.IMAGE_ID, A.SALES_PRICE,A.STOCK_NUM ,A.MAIN_DESCRIPTION ,
                B.SUPPLIERNUM, B.NAME_GROUP
                from T_EC_M_GOODS as A left join 
                (SELECT "
                           . "COUNT(E.SUPPLIER_NO) AS SUPPLIERNUM, "
                           . "GROUP_CONCAT(F.SUPPLIER_NAME SEPARATOR '、') AS NAME_GROUP, "
                           . "E.GOODS_NO "
                           . "FROM T_EC_M_GOODS_REALATED_SUPPLIER AS E "
                           . "LEFT OUTER JOIN T_AD_M_SUPPLIER AS F ON E.SUPPLIER_NO = F.SUPPLIER_NO "
                           . "GROUP BY E.GOODS_NO "
                           . "ORDER BY E.GOODS_NO "
                           . ") "
                           . " AS B ON A.GOODS_NO = B.GOODS_NO 
                            where GOODS_TP = '1' ";
        $sql .= "LIMIT " . "10" . " OFFSET " . "0";
        $arrGoods = $this->select($sql, null, dbReturnType::ALL);
        return $arrGoods;
    }

    public function pbGetSearchCount($arrCondition) {
        $sql = "select count(*) from T_EC_M_GOODS as A left join (SELECT "
                           . "COUNT(X.SUPPLIER_NO) AS SUPPLIERNUM, "
                           . "GROUP_CONCAT(Y.SUPPLIER_NAME SEPARATOR '、') AS NAME_GROUP, "
                           . "X.GOODS_NO "
                           . "FROM T_EC_M_GOODS_REALATED_SUPPLIER AS X "
                           . "LEFT OUTER JOIN T_AD_M_SUPPLIER AS Y ON X.SUPPLIER_NO = Y.SUPPLIER_NO "
                           . "GROUP BY X.GOODS_NO "
                           . "ORDER BY X.GOODS_NO "
                           . ") "
                           . " AS B ON A.GOODS_NO = B.GOODS_NO " 
                . "left join T_EC_M_GOODS_REALATED_CATG as D
                on A.GOODS_NO = D.GOODS_NO left join T_EC_M_GOODS_SECOND_CATG as E on D.SECOND_CATG_NO = E.SECOND_CATG_NO 
                 where  A.GOODS_TP = '1' ";
        if ($arrCondition["GoodsName"] != null && $arrCondition["GoodsName"] != "" ) {
            $sql .= "and A.GOODS_NAME LIKE CONCAT('%' , :GoodsName , '%') "; 
            $arrParameter["GoodsName"] = $arrCondition["GoodsName"];
        }
        if ($arrCondition["GoodsCode"] != null && $arrCondition["GoodsCode"] != "" ) {
            $sql .= "and A.GOODS_CODE LIKE CONCAT('%' , :GoodsCode , '%') "; 
            $arrParameter["GoodsCode"] = $arrCondition["GoodsCode"];
        }
        if ($arrCondition["FirstCatg"] != null && $arrCondition["FirstCatg"] != "" ) {
            $sql .= "and E.FIRST_CATG_NO = :FirstCatg "; 
            $arrParameter["FirstCatg"] = $arrCondition["FirstCatg"];
        }
        if ($arrCondition["SecondCatg"] != null && $arrCondition["SecondCatg"] != "" ) {
            $sql .= "and E.SECOND_CATG_NO = :SecondCatg "; 
            $arrParameter["SecondCatg"] = $arrCondition["SecondCatg"];
        }
        if ($arrCondition["PriceFrom"] != null && $arrCondition["PriceFrom"] != "" ) {
            $sql .= "and A.SALES_PRICE >= :PriceFrom "; 
            $arrParameter["PriceFrom"] = $arrCondition["PriceFrom"];
        }
        if ($arrCondition["PriceTo"] != null && $arrCondition["PriceTo"] != "" ) {
            $sql .= "and A.SALES_PRICE <= :PriceTo "; 
            $arrParameter["PriceTo"] = $arrCondition["PriceTo"];
        }
        if ($arrCondition["StockNumFg"] == "1" ) {
            $sql .= "and A.STOCK_NUM > '0' "; 
        } else if($arrCondition["StockNumFg"] == "2") {
            $sql .= "and A.STOCK_NUM <= '0' "; 
        } else if ($arrCondition["StockNumFg"] == "3") {
            if($arrCondition["StockNumFrom"] != null && $arrCondition["StockNumFrom"] != "") {
                $sql .= "and A.STOCK_NUM >= :StockNumFrom "; 
                $arrParameter["StockNumFrom"] = $arrCondition["StockNumFrom"];
            }
            if($arrCondition["StockNumTo"] != null && $arrCondition["StockNumTo"] != "") {
                $sql .= "and A.STOCK_NUM <= :StockNumTo "; 
                $arrParameter["StockNumTo"] = $arrCondition["StockNumTo"];
            }
        } else {}
        $count = $this->select($sql, $arrParameter, dbReturnType::ONE);
        return $count;
    }

    public function pbSearchGoods($arrCondition) {
        $sql = "select A.GOODS_CODE, A.GOODS_NO, A.GOODS_NAME,A.VARIATION, A.IMAGE_ID, A.SALES_PRICE,A.STOCK_NUM ,A.MAIN_DESCRIPTION ,
                B.SUPPLIERNUM, B.NAME_GROUP,A.STOCK_MIN,A.STOCK_MAX,A.SUGGEST_TP,A.DATA_VERSION
                 from T_EC_M_GOODS as A left outer join (SELECT "
                           . "COUNT(X.SUPPLIER_NO) AS SUPPLIERNUM, "
                           . "GROUP_CONCAT(Y.SUPPLIER_NAME SEPARATOR '、') AS NAME_GROUP, "
                           . "X.GOODS_NO "
                           . "FROM T_EC_M_GOODS_REALATED_SUPPLIER AS X "
                           . "LEFT OUTER JOIN T_AD_M_SUPPLIER AS Y ON X.SUPPLIER_NO = Y.SUPPLIER_NO "
                           . "GROUP BY X.GOODS_NO "
                           . "ORDER BY X.GOODS_NO "
                           . ") "
                           . " AS B ON A.GOODS_NO = B.GOODS_NO " 
                ." left join T_EC_M_GOODS_REALATED_CATG as D
                on A.GOODS_NO = D.GOODS_NO left join T_EC_M_GOODS_SECOND_CATG as E on D.SECOND_CATG_NO = E.SECOND_CATG_NO 
                 where A.GOODS_TP = '1' ";
        if ($arrCondition["GoodsName"] != null && $arrCondition["GoodsName"] != "" ) {
            $sql .= "and A.GOODS_NAME LIKE CONCAT('%' , :GoodsName , '%') "; 
            $arrParameter["GoodsName"] = $arrCondition["GoodsName"];
        }
        if ($arrCondition["GoodsCode"] != null && $arrCondition["GoodsCode"] != "" ) {
            $sql .= "and A.GOODS_CODE LIKE CONCAT('%' , :GoodsCode , '%') "; 
            $arrParameter["GoodsCode"] = $arrCondition["GoodsCode"];
        }
        if ($arrCondition["FirstCatg"] != null && $arrCondition["FirstCatg"] != "" ) {
            $sql .= "and E.FIRST_CATG_NO = :FirstCatg "; 
            $arrParameter["FirstCatg"] = $arrCondition["FirstCatg"];
        }
        if ($arrCondition["SecondCatg"] != null && $arrCondition["SecondCatg"] != "" ) {
            $sql .= "and E.SECOND_CATG_NO = :SecondCatg "; 
            $arrParameter["SecondCatg"] = $arrCondition["SecondCatg"];
        }
        if ($arrCondition["PriceFrom"] != null && $arrCondition["PriceFrom"] != "" ) {
            $sql .= "and A.SALES_PRICE >= :PriceFrom "; 
            $arrParameter["PriceFrom"] = $arrCondition["PriceFrom"];
        }
        if ($arrCondition["PriceTo"] != null && $arrCondition["PriceTo"] != "" ) {
            $sql .= "and A.SALES_PRICE <= :PriceTo "; 
            $arrParameter["PriceTo"] = $arrCondition["PriceTo"];
        }
        if ($arrCondition["StockNumFg"] == "1" ) {
            $sql .= "and A.STOCK_NUM > '0' "; 
        } else if($arrCondition["StockNumFg"] == "2") {
            $sql .= "and A.STOCK_NUM <= '0' "; 
        } else if ($arrCondition["StockNumFg"] == "3") {
            if($arrCondition["StockNumFrom"] != null && $arrCondition["StockNumFrom"] != "") {
                $sql .= "and A.STOCK_NUM >= :StockNumFrom "; 
                $arrParameter["StockNumFrom"] = $arrCondition["StockNumFrom"];
            }
            if($arrCondition["StockNumTo"] != null && $arrCondition["StockNumTo"] != "") {
                $sql .= "and A.STOCK_NUM <= :StockNumTo "; 
                $arrParameter["StockNumTo"] = $arrCondition["StockNumTo"];
            }
        } else {}
        if ($arrCondition["orderColumn"] != null && $arrCondition["orderColumn"] != "") {
            $sql .= "order by ". $arrCondition["orderColumn"]. " ". $arrCondition["order"]. " ";
        } else {
            $sql .= "order by A.GOODS_NO ";
        }
        $sql .= "LIMIT " . $arrCondition["limit"] . " OFFSET " . $arrCondition["offset"];
        $arrGoods = $this->select($sql, $arrParameter, dbReturnType::ALL);
        return $arrGoods;
    }

    public function pbGetGoodsInfo($strGoodsNo) {
        $sql = "select * from T_EC_M_GOODS as A left join T_EC_M_GOODS_REALATED_CATG as D
                on A.GOODS_NO = D.GOODS_NO left join T_EC_M_GOODS_SECOND_CATG as E on D.SECOND_CATG_NO = E.SECOND_CATG_NO 
                where A.GOODS_NO = :GoodsNo ";
        $parameter = array("GoodsNo" => $strGoodsNo);
        $arrGoodsInfo = $this->select($sql, $parameter, dbReturnType::ROW);
        return $arrGoodsInfo;
    }

    public function pbGoodsInsert($arrParameter) {
        $sql = "insert into T_EC_M_GOODS (GOODS_CODE,
                                          GOODS_NAME,
                                          SALES_PRICE,
                                          VARIATION,
                                          STOCK_NUM,
                                          GOODS_TP )
                                   values(:GoodsCode,
                                          :GoodsName,
                                          :SellPrice,
                                          :Variation,
                                          :StockNum ,
                                          :GoodsTp) ";
        $parameter = array("GoodsCode" => $arrParameter["GoodsCode"],
                            "GoodsName" => $arrParameter["GoodsName"],
                            "Variation" => $arrParameter["Variation"],
                            "SellPrice" => $arrParameter["SellPrice"],
                            "GoodsTp" => 1,
                            "StockNum" => $arrParameter["StockNum"]);
        $strGoodsNo = $this->insert($sql, $parameter);
        return $strGoodsNo;
    }

    public function pbInsertGoods($arrProductInfo) {
        $sql = "insert into T_EC_M_GOODS (GOODS_CODE,
                                          GOODS_NAME,
                                          GOODS_TP )
                                   values(:GoodsCode,
                                          :GoodsName,
                                          :GoodsTp ) ";
        $parameter = array("GoodsCode" => $arrProductInfo["ProductCode"],
                            "GoodsName" => $arrProductInfo["ProductName"],
                            "GoodsTp" => $arrProductInfo["ProductTp"]);
        $strGoodsNo = $this->insert($sql, $parameter);
        return $strGoodsNo;
    }

    public function pbGoodsUpdate($arrParameter) {
        $sql = "update T_EC_M_GOODS set GOODS_CODE = :GoodsCode,
                                          GOODS_NAME = :GoodsName,
                                          SALES_PRICE = :SellPrice,
                                          VARIATION = :Variation,
                                          STOCK_NUM =:StockNum 
                              where GOODS_NO = :GoodsNo ";
        $parameter = array("GoodsCode" => $arrParameter["GoodsCode"],
                            "GoodsName" => $arrParameter["GoodsName"],
                            "Variation" => $arrParameter["Variation"],
                            "SellPrice" => $arrParameter["SellPrice"],
                            "GoodsNo" => $arrParameter["GoodsNo"],
                            "StockNum" => $arrParameter["StockNum"]);
        $strUpdateRe = $this->update($sql, $parameter);
        return $strUpdateRe;
    }

    public function pbUpdateGoods($strGoodsNo, $arrProductInfo) {
        $sql = "update T_EC_M_GOODS set GOODS_CODE = :GoodsCode,
                                          GOODS_NAME = :GoodsName,
                                          GOODS_TP =:GoodsTp 
                              where GOODS_NO = :GoodsNo ";
        $parameter = array("GoodsCode" => $arrProductInfo["ProductCode"],
                            "GoodsName" => $arrProductInfo["ProductName"],
                            "GoodsNo" => $strGoodsNo,
                            "GoodsTp" => $arrProductInfo["ProductTp"]);
        $strUpdateRe = $this->update($sql, $parameter);
        return $strUpdateRe;
    }
    public function pbGoodsDelete($strGoodsNo) {
        $sql = "delete from T_EC_M_GOODS where GOODS_NO = :GoodsNo ";
        $parameter = array("GoodsNo" =>$strGoodsNo);
        $strDeleteRe = $this->delete($sql, $parameter);
        return $strDeleteRe;
    }

    public function pbGoodsStockAlertUpdate($arrParameter) {
        $sql = "UPDATE T_EC_M_GOODS SET SUGGEST_TP = :Suggest ,
                                          STOCK_MIN = :Min ,
                                          STOCK_MAX = :Max ,
                                          DATA_VERSION = DATA_VERSION + 1
                              WHERE GOODS_NO = :Id AND DATA_VERSION = :Ver ";
        $strUpdateRe = $this->Update_T($sql, $arrParameter);
        return $strUpdateRe;
    }

    public function pbUpdateProductNum($strProductGoodsNo,$strManufacturingNum) {
        $sql = "UPDATE T_EC_M_GOODS SET STOCK_NUM = STOCK_NUM + :ManufacturingNum
                              WHERE GOODS_NO = :GoodsNo ";
        $arrParameter = array("GoodsNo" => $strProductGoodsNo,
                              "ManufacturingNum" => $strManufacturingNum);
        $strUpdateRe = $this->update($sql, $arrParameter);
        return $strUpdateRe;
    }

    public function pbUpdateGoodsNum($strGoodsNo,$strNeedNum) {
        $sql = "UPDATE T_EC_M_GOODS SET STOCK_NUM = STOCK_NUM - :NeedNum
                              WHERE GOODS_NO = :GoodsNo ";
        $arrParameter = array("GoodsNo" => $strGoodsNo,
                              "NeedNum" => $strNeedNum);
        $strUpdateRe = $this->update($sql, $arrParameter);
        return $strUpdateRe;
    }
}